﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.Common;

namespace TSCC.DataAccess
{
    public class DA_ExceptionLog:DA_Matrix
    {

        public string MSG = string.Empty;
        /// <summary>
        /// 
        /// </summary>
        /// <param name="p_csrNO"></param>
        /// <param name="p_csrName"></param>
        /// <param name="p_csrTeam"></param>
        /// <param name="p_exceptionItem"></param>
        /// <param name="p_startTime"></param>
        /// <param name="p_endTime"></param>
        /// <param name="p_result"></param>
        /// <param name="p_none">聯絡未果</param>
        /// <param name="p_note">備註</param>
        /// <returns></returns>
        public int Insert(string p_csrNO, string p_csrName,string p_csrTeam
            , string p_exceptionItem, string p_startTime, string p_endTime
          , int p_result, int p_none, string p_note,string p_csrDate,int p_out_time)
        {

            //TODO:先增前應該要先確認是否已有輸入過了，目前檢查日期+員編+開始時間這三個欄位不能重復

            createCommand(getInsertStatement());
            //cmd.Parameters.Add(getParameter(System.DateTime.Today, DbType.Date));
            cmd.Parameters.Add(getParameter(p_csrDate, DbType.Date));
            cmd.Parameters.Add(getParameter(p_csrNO, DbType.String));
            cmd.Parameters.Add(getParameter(p_csrName, DbType.String));
            cmd.Parameters.Add(getParameter(p_csrTeam, DbType.String));
            cmd.Parameters.Add(getParameter(p_exceptionItem, DbType.String));
            cmd.Parameters.Add(getParameter(p_startTime, DbType.String));
            cmd.Parameters.Add(getParameter(p_endTime, DbType.String));
            cmd.Parameters.Add(getParameter(p_result, DbType.Int32));
            cmd.Parameters.Add(getParameter(p_none, DbType.Int32));
            cmd.Parameters.Add(getParameter(p_note, DbType.String));

            cmd.Parameters.Add(getParameter(p_csrNO, DbType.String));
            cmd.Parameters.Add(getParameter(System.DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"), DbType.DateTime));
            cmd.Parameters.Add(getParameter(p_csrNO, DbType.String));
            cmd.Parameters.Add(getParameter(System.DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"), DbType.DateTime));
            cmd.Parameters.Add(getParameter(p_out_time,DbType.Int32));

            
            int result = 0;
            try
            {
                MSG = string.Empty;
                result = TSCC.Common.DAL.ExecuteNoneQuery(cmd);
            }
            catch(Exception ex)
            {
                MSG = ex.Message;
            }

            return result;
        }

        public int Update(string p_csrNO, string p_csrName
            , string p_exceptionItem, string p_startTime, string p_endTime
          , int p_result, int p_none, string p_note, string p_csrDate)
        {

            createCommand(getUpdateStatement());

            cmd.Parameters.Add(getParameter(p_exceptionItem, DbType.String));
            cmd.Parameters.Add(getParameter(p_result, DbType.Int32));
            cmd.Parameters.Add(getParameter(p_none, DbType.Int32));
            cmd.Parameters.Add(getParameter(p_note, DbType.String));
            cmd.Parameters.Add(getParameter(p_csrNO, DbType.String));
            cmd.Parameters.Add(getParameter(System.DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"), DbType.DateTime));

            cmd.Parameters.Add(getParameter(p_csrNO, DbType.String));
            cmd.Parameters.Add(getParameter(p_csrDate, DbType.Date));
            cmd.Parameters.Add(getParameter(p_startTime, DbType.String));

            int result = 0;
            try
            {
                MSG = string.Empty;
                result = TSCC.Common.DAL.ExecuteNoneQuery(cmd);
            }
            catch (Exception ex)
            {
                MSG = ex.Message;
            }

            return result;
        }

        public DataTable Query(string p_csrNO,string p_startDate,string p_endDate)
        {
            createCommand(getQueryStatement());

            cmd.Parameters.Add(getParameter(p_csrNO, DbType.String));
            cmd.Parameters.Add(getParameter(p_startDate, DbType.Date));
            cmd.Parameters.Add(getParameter(p_endDate, DbType.Date));

            DataTable dt = TSCC.Common.DAL.GetDataTable(cmd);
            return dt;
        }

        public int Delete(string p_csrNO, string p_startTime, string p_csrDate)
        {
            createCommand(getDelStatement());

            cmd.Parameters.Add(getParameter(p_csrNO, DbType.String));
            cmd.Parameters.Add(getParameter(p_csrDate, DbType.Date));
            cmd.Parameters.Add(getParameter(p_startTime, DbType.String));

            int result = 0;
            try
            {
                MSG = string.Empty;
                result = TSCC.Common.DAL.ExecuteNoneQuery(cmd);
            }
            catch (Exception ex)
            {
                MSG = ex.Message;
            }

            return result;
        }

        protected string getInsertStatement()
        {
            return @"INSERT INTO ExceptionLog(
                        日期,員編,姓名,組別,例外項目,例外時間起,例外時間迄
                        ,實際產能,聯絡未果,備註,init,init_time,upd,upd_time,out_time)
                    VALUES(?,?,?,?,?,?,?
                        ,?,?,?,?,?,?,?,?)
";
        }

  
        

        protected void getInsertParameterCollection()
        {
            DbType[] dbTypeCollection = { DbType.Date };
            //DAL.GetParameter("@p_telephone", DbType.String, p_telephone, cmd.CreateParameter());
            /*
              para1.DbType = DbType.Date;
              para1.Value = System.DateTime.Today;
              para1.ParameterName = string.Empty;
             */

        }

        protected string getUpdateStatement()
        {
            return @"UPDATE ExceptionLog SET 
                            例外項目 = ?
                            ,實際產能 = ?
                            ,聯絡未果 = ?
                            ,備註 = ?
                            ,upd = ?
                            ,upd_time = ?
                        WHERE 員編=?
                        AND 日期=?
                        AND 例外時間起=?        
";
        }

        protected string getQueryStatement()
        {
            return @"SELECT 
                        * 
                    FROM ExceptionLog 
                    WHERE 1=1 
                    AND 員編 = ?
                    AND (日期 BETWEEN ? AND ?)

";
        }

        protected string getDelStatement()
        {
            return @"DELETE From ExceptionLog
                        WHERE 員編 = ?
                        AND 日期 = ?
                        AND 例外時間起 = ?    ";
        }
    }
}
